
package hk.ces.oxfam.patch.patchs;

import hk.ces.oxfam.patch.DBPatch;
import hk.ces.oxfam.patch.db.sql.SQLUtil;
import hk.ces.oxfam.patch.db.result.BeanResult;
import hk.ces.oxfam.patch.db.result.ExcuteResult;
import hk.ces.oxfam.patch.db.result.SQLResult;
import hk.ces.oxfam.patch.db.sql.PreparedSQL;
import hk.ces.oxfam.patch.obj.BatchItem;
import hk.ces.oxfam.patch.obj.BeanPacker;
import hk.ces.oxfam.patch.xml.BeanResultXML;
import hk.ces.oxfam.patch.xml.BeanToXML;
import hk.ces.oxfam.patch.xml.factory.SQLResultXMLFactory;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.log4j.Logger;

/**
 *
 * @author ReutyCheng
 */
public class OnlineDonationBatchStatusPatch implements DBPatch
{
  private static final String BATCH_MATCHED = "MATCHED";
  private static final String BATCH_COMPLETED = "COMPLETED";
  private static final String ONLINE_PAYMENT = "ONLINE_PAYMENT";

  public String getName()
  {
    return "Update Batch Status from [MATCHED] to [COMPLETED] with Online Donation DB Patch";
  }

  public void patch(Connection connection) throws SQLException
  {
    BeanResult<BatchItem> result = this.getAllOnlineMatchedBatch(connection);
    SQLResultXMLFactory factory = new SQLResultXMLFactory("OnlineMatchedBatch.xml");
    factory.setXMLItems(new BeanResultXML(result, new BeanToXML<BatchItem>()
    {
      public Object[] values(BatchItem bean)
      {
        return new Object[]{bean.getId(), bean.getBatchNo()};
      }

      public String[] columns()
      {
        return new String[]{"ID", "BATCH_NO"};
      }
    }));
    factory.writeToFile();
    
    ExcuteResult excuteResult = this.updateOnlineDonationBatchStatus(connection);
    Logger.getLogger(this.getClass()).debug(excuteResult.getExcuteStatus() + " : Totally update Batch[count=" + excuteResult.getResultCount() + "]");
  }

  private ExcuteResult updateOnlineDonationBatchStatus(Connection connection) throws SQLException
  {
    String sqlOne = "select distinct batch_id from batch_paymentinfo where paymentinfos_id in (select id from paymentinfo where disabled = ? and paymentMethod = ? and verified = ?)";
    String sqlTwo = "select temp.batch_id from (select batch_id, count(paymentinfos_id) as paymentCount from batch_paymentinfo group by batch_id) as temp where temp.paymentCount = 1";
    String selectMainSql = "select id from batch where disabled = ? and batchstatus = ? and id in ((" + sqlOne + ") intersect (" + sqlTwo + "))";

    String updateMainSql = "update batch set batchstatus = ? where id in (" + selectMainSql + ")";
    Object[] objects = new Object[] {
      BATCH_COMPLETED,
      Boolean.FALSE,
      BATCH_MATCHED,
      false,
      ONLINE_PAYMENT,
      true
    };
    ExcuteResult result = SQLUtil.execute(connection, new PreparedSQL(updateMainSql, objects));
    return result;
  }
  
  private BeanResult<BatchItem> getAllOnlineMatchedBatch(Connection connection) throws SQLException
  {
    String sqlOne = "select distinct batch_id from batch_paymentinfo where paymentinfos_id in (select id from paymentinfo where disabled = ? and paymentMethod = ? and verified = ?)";
    String sqlTwo = "select temp.batch_id from (select batch_id, count(paymentinfos_id) as paymentCount from batch_paymentinfo group by batch_id) as temp where temp.paymentCount = 1";
    String mainSql = "select id, batchNo from batch where disabled = ? and batchstatus = ? and id in ((" + sqlOne + ") intersect (" + sqlTwo + "))";
    Object[] objects = new Object[] {
      Boolean.FALSE,
      BATCH_MATCHED,
      false,
      ONLINE_PAYMENT,
      true
    };

    BeanResult<BatchItem> beanResult = new BeanResult<BatchItem>(SQLUtil.query(connection, new PreparedSQL(mainSql, objects)))
    {
      @Override
      public BeanPacker<BatchItem> packer(final SQLResult result)
      {
        return new BeanPacker<BatchItem>()
        {
          public BatchItem pack(Object[] objects)
          {
            BatchItem item = new BatchItem();
            item.setId((Long) objects[0]);
            item.setBatchNo((String) objects[1]);

            return item;
          }
        };
      }
    };

    return beanResult;
  }
}
